/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Lop;

import conn.JavaConnectDb;
import java.sql.Connection;
import java.util.Date;
import javax.swing.JOptionPane;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;


/**
 *
 * @author lhqbao
 */
public class lKHU_TDC {
    private String makhu;
    private String tenkhu;

   
    private Date ngaydvsd;
    private lDIACHI dchi;
    public lKHU_TDC()
    {
        
    }
    public lKHU_TDC(String makhu, String tenkhu, Date ngaydvsd, lDIACHI dchi)
    {
        this.makhu = makhu;
        this.tenkhu = tenkhu;
        this.ngaydvsd = ngaydvsd;
        this.dchi = dchi;
    }

    public String getMakhu() {
        return makhu;
    }

    public void setMakhu(String makhu) {
        this.makhu = makhu;
    }

    public String getTenkhu() {
        return tenkhu;
    }

    public void setTenkhu(String tenkhu) {
        this.tenkhu = tenkhu;
    }

    public Date getNgaydvsd() {
        return ngaydvsd;
    }

    public void setNgaydvsd(Date ngaydvsd) {
        this.ngaydvsd = ngaydvsd;
    }
     public lDIACHI getDchi() {
        return dchi;
    }

    public void setDchi(lDIACHI dchi) {
        this.dchi = dchi;
    }
    
    OraclePreparedStatement pst;
    OracleResultSet rs;
    
    public OracleResultSet getDsKHU_TDC(OConnection cnn){
        try{
            
            String sql = "select k.makhu, k.tenkhu, k.ngaydvsd, k.dchi.so as so ,k.dchi.duong as duong, k.dchi.phuongxa as phuongxa, k.dchi.quanhuyen as quanhuyen from KHU_TDC k";

            //String sql = "select * from quanhuyen";
            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql, OracleResultSet.TYPE_SCROLL_INSENSITIVE, OracleResultSet.CONCUR_UPDATABLE);
            rs = (OracleResultSet) pst.executeQuery();
         }catch(Exception e){
            JOptionPane.showMessageDialog(null,e);
        }
        return rs;    
    } 
    public OracleResultSet hienthiKHU_TDC(OConnection cnn){
        try{
            
            String sql = "select k.makhu, k.tenkhu ,to_char(k.ngaydvsd, 'DD-MM-YYYY') as ngaydvsd, k.dchi.so as so ,k.dchi.duong as duong, k.dchi.phuongxa as phuongxa, k.dchi.quanhuyen as quanhuyen from KHU_TDC k";

            //String sql = "select * from quanhuyen";
            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql, OracleResultSet.TYPE_SCROLL_INSENSITIVE, OracleResultSet.CONCUR_UPDATABLE);
            rs = (OracleResultSet) pst.executeQuery();
         }catch(Exception e){
            JOptionPane.showMessageDialog(null,e);
        }
        return rs;    
    } 
    public OracleResultSet getDS_NHOMNEN(OConnection cnn){
        try{
            
            String sql = "select manhom, tennhom from NHOM_NEN where refKHU_TDC = (select ref(p) from KHU_TDC p where p.makhu=?)";

            //String sql = "select * from quanhuyen";
            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql, OracleResultSet.TYPE_SCROLL_INSENSITIVE, OracleResultSet.CONCUR_UPDATABLE);
            pst.setString(1, this.makhu);
            rs = (OracleResultSet) pst.executeQuery();
         }catch(Exception e){
            JOptionPane.showMessageDialog(null,e);
        }
        return rs;    
    } 
    public void themKHU_TDC(OConnection cnn)
    {
        try{
            //String sql = "insert into KHU_TDC values(T_KHU_TDC('A1','KHU A1','10-NOV-2014',DIACHI('SO 1',' QUOC LO 91 B','AN KHANH','QUAN NINH KIEU')))";
            String sql = "insert into KHU_TDC values(T_KHU_TDC(?,?,TO_DATE(?,'dd-MM-yyyy'),DIACHI(?,?,?,?)))";

            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql);
            pst.setString(1, this.makhu);
            pst.setString(2, this.tenkhu);
            pst.setDate(3, (java.sql.Date)this.ngaydvsd);   
            pst.setString(4, this.dchi.getSo());
            pst.setString(5, this.dchi.getDuong());
            pst.setString(6, this.dchi.getPhuongxa());
            pst.setString(7, this.dchi.getQuanhuyen());
            pst.executeQuery();
                    
         }catch(Exception e){
            JOptionPane.showMessageDialog(null, e);
        }
    }
    
    public void suaKHU_TDC(OConnection cnn)
    {
       
        try{

            String sql = "UPDATE KHU_TDC k  SET k.TENKHU = ?, k.ngaydvsd = TO_DATE(?,'DD-MM-YYYY'), k.dchi.so= ?, k.dchi.duong = ?, k.dchi.phuongxa = ?, k.dchi.quanhuyen= ? WHERE k.makhu = ?";
            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql);
            pst.setString(1, this.tenkhu);
            pst.setDate(2, (java.sql.Date)this.ngaydvsd);
            pst.setString(3, this.dchi.getSo());
            pst.setString(4, this.dchi.getDuong());
            pst.setString(5, this.dchi.getPhuongxa());
            pst.setString(6, this.dchi.getQuanhuyen());
            pst.setString(7, this.makhu);
            pst.executeQuery();

        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e);
        } 
    }    
    public void xoaKHU_TDC(OConnection cnn)
    {
        
        try{
            String sql = "delete from KHU_TDC where makhu=?";
            pst=(OraclePreparedStatement) cnn.createConnection().prepareStatement(sql);
            pst.setString(1, this.makhu);
            pst.executeQuery();
            
         }catch(Exception e)
         {
            JOptionPane.showMessageDialog(null, e);
         }
   }
    
}